Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Both the data files and the indexes should be striped over as many disk drives as necessary to achieve optimal I/O rates on those disks. From Chapter 14, “Advanced Disk I/O Concepts,” remember that you can only push a disk drive to a maximum random I/O rate.

When you use a disk array, the data is automatically striped across all the disk drives; therefore, it is necessary to create only one tablespace and table for all your data. You do not even have to put indexes into another tablespace—although I recommend doing so for other reasons (such as monitoring and maintenance).

With traditional disks, it is difficult to manage hundreds of data files and disks; with a disk array, you can manage hundreds of disks with just a few data files. Of course, Oracle has a 2 gigabyte limitation on the size of a data file, but this is easily resolved by creating a data file for every 2 gigabytes of space you need. The data files can all reside on the same disk array volume.

If you use a disk array, many of the management tasks and load balancing tasks are greatly simplified. With the disk array, you also have the option of using fault tolerance without affecting system performance. Of course, using fault tolerance requires significantly more disks.

I recommend that you use a disk array if possible. Software striping is fine, but if your system is under heavy loads (as is typical in a system used for BLOB data), you can achieve better performance by offloading the striping overhead to a hardware RAID controller.

Hardware Considerations

When choosing hardware to use for a system used for BLOB data, consider these factors:

  Low user load. Not many concurrent processes/threads simultaneously access the system.
  High I/O load. I/Os are concurrent and heavy; they are mostly random I/O with a size of multiblock reads. The I/O pattern requested by an individual process is sequential, but because there are multiple data streams, the data access becomes more random.
  Extremely high network traffic. Because the idea of BLOB systems is to take large amounts of data and quickly transfer that data across the network, the amount of traffic is usually very high.

Because this type of data access does not typically involve heavy CPU activity, you may not see any benefit from multiple CPUs. It is much more important to have a system that can handle extreme amounts of I/O and network processing. This is not to say that the CPU activity will not be high, but the BLOB system is not as CPU bound as is a typical OLTP or DSS system.

With this type of access to the disks, you can benefit from a disk array. I prefer hardware striping to OS striping because it does not incur any additional overhead for the operating system and does not take up valuable CPU cycles. If hardware striping is not available, OS striping is adequate.

Because network traffic is very high, you must have a sufficient network bandwidth to handle the load. A high-speed network solution such as 100Base-T or fiber optics can benefit the overall performance of the system.

Tuning Considerations

The system used for BLOB access is designed to give optimal access to large amounts of data as quickly as possible. It is also frequently a requirement that the data streams be continuous for both video and audio data.

You may have to tune both Oracle and the server operating system. The following sections look first at Oracle and then at the server operating system.

Oracle Tuning

Carefully analyze these things to determine whether adjustment to these parameters is necessary:

  DB_BLOCK_BUFFERS. Because such large amounts of data are accessed either frequently or infrequently, it is hard to determine whether the SGA is effective for caching data. Unless the same BLOB data is accessed by several users, the SGA is typically not useful for data caching.
  Library cache. Check the V$LIBRARYCACHE table, which contains statistics about how well you are using the library cache. The important columns to view in this table are PINS and RELOADS. A low number of reloads relative to the number of executions indicates a high cache-hit rate. You can reduce the library cache misses by increasing the amount of memory available for the library cache. Do this by increasing the Oracle tunable parameter SHARED_POOL_SIZE. Try increasing the size of the shared pool by 10 percent and monitor it again. If this is not sufficient, increase the size of the shared pool by another 10 percent and continue in this fashion.
  Multiblock reads. Because the BLOB data is accessed sequentially, the size of the multiblock read is important. The number of blocks read in a multiblock read is specified by the Oracle initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. This value, multiplied by the DB_BLOCK_SIZE parameter, results in the size of the I/O. A good value for this initialization parameter is 64K or larger; try setting this parameter as high as Oracle allows. The maximum size is platform dependent; check your Oracle documentation for this value.
  Data dictionary cache. To check the efficiency of the data dictionary cache, look at the dynamic performance table V$ROWCACHE. The important columns to view in this table are GETS and GETMISSES. The ratio of GETMISSES to GETS should be low.
  Rollback segments. Unless you do a significant amount of updates, rollback contention and the number of rollback segments is usually not important in BLOB systems.
  Latch contention. Unless you do a significant amount of updates, latches are not an issue in a BLOB system.
  Checkpoints. Unless the number of updates is significant, the checkpoint processes is usually not an issue for this type of system.
  Archiving. As with the other redo log-related parameters, because the BLOB system is primarily used for reads, archiving performance is usually not a problem.

Pay particular attention to these areas when tuning a system for BLOB access. Probably the area that requires the most attention is the I/O subsystem. By optimizing the I/O subsystem, you will see performance increases.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.